#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
用户管理模块数据表创建脚本
用于创建和初始化用户管理相关的补充表
"""

import pymysql
import os
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()

# 数据库连接信息
DB_HOST = os.getenv('DB_HOST', 'localhost')
DB_PORT = int(os.getenv('DB_PORT', 3306))
DB_USER = os.getenv('DB_USER', 'root')
DB_PASSWORD = os.getenv('DB_PASSWORD', '')
DB_NAME = os.getenv('DB_NAME', 'ai_teach')

# 连接数据库
conn = pymysql.connect(
    host=DB_HOST,
    port=DB_PORT,
    user=DB_USER,
    password=DB_PASSWORD,
    database=DB_NAME,
    charset='utf8mb4'
)

try:
    cursor = conn.cursor()
    
    # 创建权限表（如果不存在）
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS `permissions` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `module` VARCHAR(50) NOT NULL COMMENT '模块名称',
        `name` VARCHAR(100) NOT NULL COMMENT '权限名称',
        `description` VARCHAR(255) COMMENT '权限描述',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `uk_module_name` (`module`, `name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统权限表';
    """)
    
    # 创建角色权限关联表（如果不存在）
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS `role_permissions` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `role` VARCHAR(50) NOT NULL COMMENT '角色名称',
        `permission_id` INT NOT NULL COMMENT '权限ID',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY `uk_role_permission` (`role`, `permission_id`),
        FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';
    """)
    
    # 修改users表，增加必要字段（如果需要）
    try:
        cursor.execute("ALTER TABLE `users` ADD COLUMN `active` TINYINT(1) DEFAULT 1 COMMENT '账户状态: 1-活跃, 0-禁用';")
        cursor.execute("ALTER TABLE `users` ADD COLUMN `last_login` DATETIME NULL COMMENT '最后登录时间';")
        cursor.execute("ALTER TABLE `users` ADD COLUMN `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';")
        cursor.execute("ALTER TABLE `users` ADD COLUMN `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';")
    except pymysql.err.InternalError:
        print("部分字段可能已存在，跳过")
    
    # 添加基本权限数据
    permissions = [
        # 用户管理模块
        ('user_management', 'view', '查看用户列表'),
        ('user_management', 'add', '添加用户'),
        ('user_management', 'edit', '编辑用户'),
        ('user_management', 'delete', '删除用户'),
        
        # 学院管理模块
        ('college_management', 'view', '查看学院列表'),
        ('college_management', 'add', '添加学院'),
        ('college_management', 'edit', '编辑学院'),
        ('college_management', 'delete', '删除学院'),
        
        # 教师管理模块
        ('teacher_management', 'view', '查看教师列表'),
        ('teacher_management', 'add', '添加教师'),
        ('teacher_management', 'edit', '编辑教师'),
        ('teacher_management', 'delete', '删除教师'),
        
        # 资源管理模块
        ('resource_management', 'view', '查看资源列表'),
        ('resource_management', 'add', '添加资源'),
        ('resource_management', 'edit', '编辑资源'),
        ('resource_management', 'delete', '删除资源'),
        
        # AI助手模块
        ('ai_assistant', 'view', '查看AI助手'),
        ('ai_assistant', 'use', '使用AI助手'),
        
        # 创新教学模块
        ('innovative_teaching', 'view', '查看创新教学'),
        ('innovative_teaching', 'use', '使用创新教学工具')
    ]
    
    # 插入权限数据
    for module, name, description in permissions:
        try:
            cursor.execute(
                "INSERT INTO `permissions` (`module`, `name`, `description`) VALUES (%s, %s, %s)",
                (module, name, description)
            )
        except pymysql.err.IntegrityError:
            print(f"权限 {module}.{name} 已存在，跳过")
    
    # 默认角色权限分配
    # 1. 校级管理员 - 所有权限
    cursor.execute("SELECT id FROM permissions")
    all_permissions = cursor.fetchall()
    for (permission_id,) in all_permissions:
        try:
            cursor.execute(
                "INSERT INTO `role_permissions` (`role`, `permission_id`) VALUES (%s, %s)",
                ('school_admin', permission_id)
            )
        except pymysql.err.IntegrityError:
            pass
    
    # 2. 院级管理员 - 除用户管理外的所有权限
    cursor.execute("SELECT id FROM permissions WHERE module != 'user_management'")
    college_admin_permissions = cursor.fetchall()
    for (permission_id,) in college_admin_permissions:
        try:
            cursor.execute(
                "INSERT INTO `role_permissions` (`role`, `permission_id`) VALUES (%s, %s)",
                ('college_admin', permission_id)
            )
        except pymysql.err.IntegrityError:
            pass
    
    # 3. 班级管理员 - 仅教师管理和AI助手权限
    cursor.execute("SELECT id FROM permissions WHERE module IN ('teacher_management', 'ai_assistant')")
    class_admin_permissions = cursor.fetchall()
    for (permission_id,) in class_admin_permissions:
        try:
            cursor.execute(
                "INSERT INTO `role_permissions` (`role`, `permission_id`) VALUES (%s, %s)",
                ('class_admin', permission_id)
            )
        except pymysql.err.IntegrityError:
            pass
    
    # 创建默认管理员账号（如果不存在）
    cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'admin'")
    admin_count = cursor.fetchone()[0]
    
    if admin_count == 0:
        # 密码为默认的 admin123
        cursor.execute(
            "INSERT INTO users (username, password, email, role, active) VALUES (%s, %s, %s, %s, %s)",
            ('admin', 'pbkdf2:sha256:150000$lAQ8vzsR$75fee5e7aaf555eb52d0b648680b585f0273e439565242ea6881d69ed1221f18', 'admin@example.com', 'school_admin', 1)
        )
        print("已创建默认管理员账号: admin/admin123")
    
    # 提交所有更改
    conn.commit()
    print("数据表创建和初始化成功！")

except Exception as e:
    print(f"发生错误: {e}")
    conn.rollback()
    
finally:
    cursor.close()
    conn.close() 